package com.it.smartoldb.repository;

import com.it.smartoldb.entity.Appointment;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 预约数据访问层
 */
@Repository
public interface AppointmentRepository extends JpaRepository<Appointment, Long> {

    /**
     * 根据老人ID查找预约记录
     */
    List<Appointment> findByElderlyIdOrderByAppointmentTimeDesc(Long elderlyId);

    /**
     * 根据护理人员ID查找预约记录
     */
    List<Appointment> findByStaffIdOrderByAppointmentTimeDesc(Long staffId);

    /**
     * 根据状态查找预约记录
     */
    List<Appointment> findByStatusOrderByAppointmentTimeDesc(Appointment.AppointmentStatus status);

    /**
     * 分页查询老人的预约记录
     */
    Page<Appointment> findByElderlyIdOrderByAppointmentTimeDesc(Long elderlyId, Pageable pageable);

    /**
     * 分页查询护理人员的预约记录
     */
    Page<Appointment> findByStaffIdOrderByAppointmentTimeDesc(Long staffId, Pageable pageable);

    /**
     * 多条件分页查询
     */
    @Query("SELECT a FROM Appointment a WHERE " +
           "(:elderlyId IS NULL OR a.elderlyId = :elderlyId) AND " +
           "(:staffId IS NULL OR a.staffId = :staffId) AND " +
           "(:status IS NULL OR a.status = :status) AND " +
           "(:serviceType IS NULL OR a.serviceType = :serviceType) " +
           "ORDER BY a.appointmentTime DESC")
    Page<Appointment> findAppointmentsWithFilters(
            @Param("elderlyId") Long elderlyId,
            @Param("staffId") Long staffId,
            @Param("status") Appointment.AppointmentStatus status,
            @Param("serviceType") String serviceType,
            Pageable pageable
    );

    /**
     * 查询指定时间范围内的预约
     */
    @Query("SELECT a FROM Appointment a WHERE a.appointmentTime BETWEEN :startTime AND :endTime " +
           "ORDER BY a.appointmentTime")
    List<Appointment> findByAppointmentTimeBetween(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime
    );

    /**
     * 查询护理人员在指定时间的预约（检查时间冲突）
     */
    @Query("SELECT a FROM Appointment a WHERE a.staffId = :staffId " +
           "AND a.status IN ('CONFIRMED', 'IN_PROGRESS') " +
           "AND ((a.appointmentTime <= :endTime AND a.endTime >= :startTime))")
    List<Appointment> findConflictingAppointments(
            @Param("staffId") Long staffId,
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime
    );

    /**
     * 统计各状态的预约数量
     */
    @Query("SELECT a.status, COUNT(a) FROM Appointment a GROUP BY a.status")
    List<Object[]> countByStatus();

    /**
     * 统计各服务类型的预约数量
     */
    @Query("SELECT a.serviceType, COUNT(a) FROM Appointment a GROUP BY a.serviceType")
    List<Object[]> countByServiceType();

    /**
     * 查询即将到来的预约（未来24小时内）
     */
    @Query("SELECT a FROM Appointment a WHERE a.status = 'CONFIRMED' " +
           "AND a.appointmentTime BETWEEN :now AND :tomorrow " +
           "ORDER BY a.appointmentTime")
    List<Appointment> findUpcomingAppointments(
            @Param("now") LocalDateTime now,
            @Param("tomorrow") LocalDateTime tomorrow
    );

    /**
     * 查询待确认的预约
     */
    List<Appointment> findByStatusOrderByCreatedAtAsc(Appointment.AppointmentStatus status);

    /**
     * 按日期统计预约数量（用于趋势图）
     */
    @Query("SELECT DATE(a.createdAt) as createdDate, COUNT(a) as count " +
           "FROM Appointment a " +
           "WHERE a.createdAt >= :startDate " +
           "GROUP BY DATE(a.createdAt) " +
           "ORDER BY createdDate")
    List<Object[]> countByDateRange(@Param("startDate") LocalDateTime startDate);
} 